﻿using System;
using System.Collections.Generic;
using System.Linq;
using orienta.Models;
using orienta.ViewModels;
using orienta.Utils;
using System.Dynamic;
using System.Reflection;
using System.Data.EntityClient;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace orienta.Helper
{

    public class SQLHelper
    {
       
        

        public static IList<dynamic> executeStore(string queryString, String[] parametros, String[] nombreParametros)
        {
            string[] arr = ConfigurationManager.ConnectionStrings["GoodTireEntities"].ConnectionString.Split(' ');

            //String connectionString = "Data Source=sql1101.shared-servers.com,1089;Initial Catalog=GoodTire;User ID=GoodTire;Password=GoodTire";
            String[] arrConnectionString = ("Data " + arr[3] + " " + arr[4] + " " + arr[5] + " " + arr[6] + " " + arr[7]).Split(';');
            String connectionString = arrConnectionString[0] + ";" + arrConnectionString[1] + ";" + arrConnectionString[2] + ";" + arrConnectionString[3] + ";" + arrConnectionString[4];
            List<dynamic> lista = new List<dynamic>();

            //queryString = "dbo." + queryString;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.CommandType = CommandType.StoredProcedure;

                for (var i = 0; i < parametros.Length; i++)
                {
                    SqlParameter param = new SqlParameter();
                    param.ParameterName = nombreParametros[i];
                    param.SqlDbType = SqlDbType.NVarChar;
                    param.Direction = ParameterDirection.Input;
                    param.Value = parametros[i];
                    command.Parameters.Add(param);
                }

                connection.Open();
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                List<String> columnas = new List<String>();

                dynamic columna1 = new ExpandoObject();
                var primero = columna1 as IDictionary<String, object>;

                //decimal total = 0;

                while (reader.Read())
                {
                    dynamic columna = new ExpandoObject();
                    var col = columna as IDictionary<String, object>;

                        for (int j = 0; j < reader.FieldCount; j++)
                        {
                            col[reader.GetName(j)] = reader.GetValue(j);

                        }
                        lista.Add(col);


                }

            }

            return lista;
        }


        public static IList<dynamic> executeStore(string queryString, Object[] parametros, String[] nombreParametros , SqlDbType[] sqltipo)
        {
            string[] arr = ConfigurationManager.ConnectionStrings["GoodTireEntities"].ConnectionString.Split(' ');

           // String connectionString = "Server=SONY-PC\\DB_ORIENTA;Database=master;Trusted_Connection=Yes;";

            String[] arrConnectionString = ("Data " + arr[3] + " " + arr[4] + " " + arr[5] + " " + arr[6] + " " + arr[7]).Split(';');
            String connectionString = arrConnectionString[0] + ";" + arrConnectionString[1] + ";" + arrConnectionString[2] + ";" + arrConnectionString[3] + ";" + arrConnectionString[4];
            List<dynamic> lista = new List<dynamic>();

            //queryString = "dbo." + queryString;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.CommandType = CommandType.StoredProcedure;

                for (var i = 0; i < parametros.Length; i++)
                {
                    SqlParameter param = new SqlParameter();
                    param.ParameterName = nombreParametros[i];
                    param.SqlDbType = sqltipo[i];
                    param.Direction = ParameterDirection.Input;
                    param.Value = parametros[i];
                    command.Parameters.Add(param);
                }

                connection.Open();
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                List<String> columnas = new List<String>();

                dynamic columna1 = new ExpandoObject();
                var primero = columna1 as IDictionary<String, object>;

                //decimal total = 0;

                while (reader.Read())
                {
                    dynamic columna = new ExpandoObject();
                    var col = columna as IDictionary<String, object>;

                    for (int j = 0; j < reader.FieldCount; j++)
                    {
                        col[reader.GetName(j)] = reader.GetValue(j);

                    }
                    lista.Add(col);


                }

            }

            return lista;
        }


    }



}